package DAO;

import tools.JDBCTools;
import vo.News;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

//属性：标题，链接地址，作者，发稿时间，浏览人数
//title address author fabushijian click
public class NewsDAO {
    //每个DAO通常都应该有四个方法
    //按序号查询，返回查询结果（对象），如果对象为null，表示没找到
    public static News getFromId(int id) throws Exception{
        Connection con = JDBCTools.getConnection();
        String sql = "select * from t_news where id = ?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1, String.valueOf(id));
        ResultSet rs = pst.executeQuery();
        News currentNews = new News();
        if(rs.next()) {
            currentNews.setId(Integer.parseInt(rs.getString("id")));
            currentNews.setTitle(rs.getString("title"));
            currentNews.setAddress(rs.getString("address"));
            currentNews.setAuthor(rs.getString("author"));
            currentNews.setFabushijian(rs.getString("fabushijian"));
            currentNews.setClick(Integer.parseInt(rs.getString("click")));
        } else {
            currentNews = null;// 若未找到用户记录，则将 currentUser 设置为 null
        }
        JDBCTools.close(con);
        return currentNews;
    }
    //按标题查询，返回查询结果（对象），如果对象为null，表示没找到
    public static News getFromTitle(String title) throws Exception{
        Connection con = JDBCTools.getConnection();
        String sql = "select * from t_news where title = ?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1,title);
        ResultSet rs = pst.executeQuery();
        News currentNews = new News();
        if(rs.next()) {
            currentNews.setId(Integer.parseInt(rs.getString("id")));
            currentNews.setTitle(rs.getString("title"));
            currentNews.setAddress(rs.getString("address"));
            currentNews.setAuthor(rs.getString("author"));
            currentNews.setFabushijian(rs.getString("fabushijian"));
            currentNews.setClick(Integer.parseInt(rs.getString("click")));
        } else {
            currentNews = null;// 若未找到用户记录，则将 currentUser 设置为 null
        }
        JDBCTools.close(con);
        return currentNews;
    }
    //按作者查询，返回查询结果（对象），如果对象为null，表示没找到
    public static ArrayList<News> getFromAuthor(String author) throws Exception{
        Connection con = JDBCTools.getConnection();
        ArrayList<News> newses = new ArrayList<>();
        String sql = "select * from t_news where author = ?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1,author);
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            News newsResult = new News(Integer.parseInt(rs.getString("id")),
                    rs.getString("title"), rs.getString("address"),
                    rs.getString("author"), rs.getString("fabushijian"),
                    Integer.parseInt(rs.getString("click")));
            newses.add(newsResult);
        }
        JDBCTools.close(con);
        return newses;
    }
    //按任意条件组合（and）查询，返回查询结果（对象集合），如果不需要该条件，则相应属性值为null或者''
    //以下提供标题（附加作者）的查找
    public static ArrayList<News> query(News news) throws Exception{
        //查询结果（对象集合）
        ArrayList<News> newses = new ArrayList<>();
        Connection con = JDBCTools.getConnection();
        String sql = "select * from t_news where title = '" + news.getTitle() + "'";
        if(news.getAuthor()!=null && !"".equals(news.getAuthor())){
            sql = sql + " and author = '" + news.getAuthor() + "'";
        }
        PreparedStatement pst = con.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            News newsResult = new News(Integer.parseInt(rs.getString("id")),
                    rs.getString("title"),rs.getString("address"),
                    rs.getString("author"),rs.getString("fabushijian"),
                    Integer.parseInt(rs.getString("click")));
            newses.add(newsResult);
        }
        JDBCTools.close(con);
        return newses;
    }
    //插入的各字段存放在对象中，返回成功或失败
    public static boolean insert(News news) throws Exception{
        boolean success = false;
        //首先检查title是否存在
        if(getFromTitle(news.getTitle())==null){//不存在，可以插入
            Connection con = JDBCTools.getConnection();
            String sql = "insert into t_news values(?,?,?,?,?,?)";
            PreparedStatement pst = con.prepareStatement(sql);
            pst.setString(1, String.valueOf(news.getId()));
            pst.setString(2,news.getTitle());
            pst.setString(3,news.getAddress());
            pst.setString(4,news.getAuthor());
            pst.setString(5,news.getFabushijian());
            pst.setString(6,String.valueOf(news.getClick()));
            int i = pst.executeUpdate();
            if(i>0) success = true;
            JDBCTools.close(con);
        }
        return success;
    }
    //提供新闻编号以删除对应新闻数据
    public static boolean update(int id,String new_title) throws Exception{
        if(getFromId(id) == null)//不存在，不可插入
            return false;
        else{
            Connection con = JDBCTools.getConnection();
            String sql = "UPDATE t_news SET title=? WHERE id = ?";
            PreparedStatement pst = con.prepareStatement(sql);
            pst.setString(1,new_title);
            pst.setString(2,String.valueOf(id));
            pst.executeUpdate();//执行删除语句
            JDBCTools.close(con);
            return true;
        }
    }
    //提供新闻编号以删除对应新闻数据
    public static boolean delete(int id) throws Exception{
        //无法查询到该用户
        if(getFromId(id) == null)//不存在，不可插入
            return false;
        else{
            Connection con = JDBCTools.getConnection();
            String sql = "Delete from t_news where id = ?";
            PreparedStatement pst = con.prepareStatement(sql);
            pst.setString(1,String.valueOf(id));
            pst.executeUpdate();//执行删除语句
            JDBCTools.close(con);
            return true;
        }
    }
    //提取所有新闻
    public static ArrayList<News> printAll() throws Exception{
        //查询结果（对象集合）
        ArrayList<News> newses = new ArrayList<>();
        Connection con = JDBCTools.getConnection();
        String sql = "select * from t_news";
        PreparedStatement pst = con.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            News newsResult = new News(Integer.parseInt(rs.getString("id")),
                    rs.getString("title"),rs.getString("address"),
                    rs.getString("author"),rs.getString("fabushijian"),
                    Integer.parseInt(rs.getString("click")));
            newses.add(newsResult);
        }
        JDBCTools.close(con);
        return newses;
    }
}
